/**
 * Licensed to the Rivulet under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *     webapps/LICENSE-Rivulet-1.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.rivues.util.data;

import java.io.IOException;
import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;

import org.apache.commons.lang.StringUtils;
import org.apache.shiro.SecurityUtils;
import org.rivu.tools.ParamTools;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.pool.DruidDataSource;
import com.googlecode.aviator.AviatorEvaluator;
import com.rivues.core.RivuDataContext;
import com.rivues.module.datamodel.web.handler.DefaultParam;
import com.rivues.module.datamodel.web.handler.DefaultValue;
import com.rivues.module.platform.web.interceptor.LogIntercreptorHandler;
import com.rivues.module.platform.web.model.Cube;
import com.rivues.module.platform.web.model.CubeLevel;
import com.rivues.module.platform.web.model.CubeMeasure;
import com.rivues.module.platform.web.model.CubeMetadata;
import com.rivues.module.platform.web.model.Database;
import com.rivues.module.platform.web.model.Dimension;
import com.rivues.module.platform.web.model.TableProperties;
import com.rivues.module.platform.web.model.TableTask;
import com.rivues.module.platform.web.model.User;
import com.rivues.util.RivuTools;
import com.rivues.util.data.ValueData;
import com.rivues.util.datasource.DataSourceTools;
import com.rivues.util.datasource.JndiDataSource;
import com.rivues.util.serialize.JSON;
import com.rivues.util.tools.H2Helper;
import com.rivues.util.tools.RivuColumnMetadata;
import com.rivues.util.tools.RivuDatabaseMetadata;
import com.rivues.util.tools.RivuTableMetaData;
import com.rivues.util.tools.TabelSql;

import freemarker.template.TemplateException;

/**
 * @author jaddy0302 Rivulet DatabaseMetaDataHandler.java 2010-3-21
 * 
 */
public class DatabaseMetaDataHandler{
	private static final Logger log = LoggerFactory.getLogger(DatabaseMetaDataHandler.class); 
	public static boolean testConnection(Database database) throws Exception{
		boolean suc = false ;
		if(database==null)
			return suc;
		{
			
			Connection conn = null ;
			try{
				if(RivuDataContext.ConnectionTypeEnum.JNDI.toString().equals(database.getConnctiontype())){
					conn = new JndiDataSource(database).getConnection();
				}else{
					Class.forName(database.getDriverclazz()) ;
					conn = DriverManager.getConnection(database.getDatabaseurl(), database.getAccount(),RivuTools.decryption(database.getPassword())) ;
				}
				if(conn!=null)
					suc = true ;
			}catch(Exception e){
				suc=false;
				throw e;
			}finally{
				if(conn!=null){
					conn.close() ;
				}
			}
		}
		return suc ;
	}
	/**
	 * 创建数据库索引
	 * @param cube 要创建索引的模型
	 * @param orgi
	 * @param tableTask 对应的表
	 * @param database 对应的数据库
	 * @throws Exception
	 */
	public static void createDatabaseIndex(Cube cube , String orgi , TableTask tableTask,Database database) throws Exception{
		for(Dimension dim : cube.getDimension()){
			for(CubeLevel level :dim.getCubeLevel()){
				if(level.isUniquemembers()){
					String indexrow = "DIM_"+level.getNameAlias().toUpperCase();
					StringBuffer sb = new StringBuffer().append("CREATE INDEX RIVUINDEX_").append(RivuTools.md5(tableTask.getTablename()+indexrow))
							.append(" ON ").append(tableTask.getTablename()).append("_VT").append(" (").append(indexrow).append(")");
					try {
						H2Helper.executeSQL(sb.toString() , database , true) ;
					} catch (Exception e) {
						log.info("索引添加失败："+sb.toString());
					}
				}
			}
		}
		
	}
	
	
	/**
	 * 获取CREATE sql语句
	 * @param metadata
	 * @return
	 */
	public static String getCreateSQL(RivuTableMetaData metadata,Database database){
		StringBuffer strb = new StringBuffer() ;
		strb.append("CREATE TABLE ") ;
		strb.append(metadata.getName().toUpperCase()).append("_VT").append("(") ;
		String sqldialect = database.getSqldialect(); 
		if("mysql".equalsIgnoreCase(sqldialect)){
			for(int i=0 ; i<metadata.getColumnMetadatas().size();i++)
			{
				RivuColumnMetadata column = metadata.getColumnMetadatas().get(i) ;
				int columnSize = 255;
				if(!column.getTypeName().toUpperCase().equals("DECIMAL")){
					columnSize =  column.getColumnSize()>255 ? column.getColumnSize():255 ;
				}else{
					columnSize = 65;
				}
				
				if(i>0){
					strb.append(",") ;
				}
				if(!column.getTypeName().toUpperCase().equals("DECIMAL") && column.getName().startsWith("MEA_")){
					 if (column.getTypeName().toUpperCase().equals("NUMBER"))
				          strb.append(column.getName()).append(" ").append(" FLOAT ").append(column.isPk() ? " PRIMARY KEY " : " ").append("NO".equalsIgnoreCase(column.getNullable()) ? " NOT NULL " : "");
				        else {
				          strb.append(column.getName()).append(" ").append(" INT ").append(column.isPk() ? " PRIMARY KEY " : " ").append("NO".equalsIgnoreCase(column.getNullable()) ? " NOT NULL " : "");
				        }
				}else{
					if(column.getName().startsWith("DIM_")){
						strb.append(column.getName()).append(" VARCHAR(").append(columnSize).append(")").append(column.isPk()?" PRIMARY KEY ":" ").append("NO".equalsIgnoreCase(column.getNullable())?" NOT NULL ":"") ;
					}else{
						strb.append(column.getName()).append(" ").append(column.getTypeName()).append("(").append(columnSize).append(")").append(column.isPk()?" PRIMARY KEY ":" ").append("NO".equalsIgnoreCase(column.getNullable())?" NOT NULL ":"") ;
					}
				}
			}
		}else{
			for(int i=0 ; i<metadata.getColumnMetadatas().size();i++)
			{
				RivuColumnMetadata column = metadata.getColumnMetadatas().get(i) ;
				int	columnSize =  column.getColumnSize()>255 ? column.getColumnSize():255 ;
				if(i>0){
					strb.append(",") ;
				}
				if(!column.getTypeName().toUpperCase().equals("DECIMAL") && column.getName().startsWith("MEA_")){
					 if (column.getTypeName().toUpperCase().equals("NUMBER"))
				          strb.append(column.getName()).append(" ").append(" FLOAT ").append(column.isPk() ? " PRIMARY KEY " : " ").append("NO".equalsIgnoreCase(column.getNullable()) ? " NOT NULL " : "");
				        else {
				          strb.append(column.getName()).append(" ").append(" INT ").append(column.isPk() ? " PRIMARY KEY " : " ").append("NO".equalsIgnoreCase(column.getNullable()) ? " NOT NULL " : "");
				        }
				}else{
					if(column.getName().startsWith("DIM_")){
						strb.append(column.getName()).append(" VARCHAR(").append(columnSize).append(")").append(column.isPk()?" PRIMARY KEY ":" ").append("NO".equalsIgnoreCase(column.getNullable())?" NOT NULL ":"") ;
					}else{
						strb.append(column.getName()).append(" ").append(column.getTypeName()).append("(").append(columnSize).append(")").append(column.isPk()?" PRIMARY KEY ":" ").append("NO".equalsIgnoreCase(column.getNullable())?" NOT NULL ":"") ;
					}
				}
			}
		}
		strb.append(")") ;
		return strb.toString() ;
	}
	/**
	 * 
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public static List<RivuTableMetaData> getTables(Database database) throws Exception{
		if(database==null)
			return new ArrayList<RivuTableMetaData>();
		List<RivuTableMetaData> tables = null ;
		{
			Class.forName(database.getDriverclazz()) ;
			Connection conn = null ;
			RivuDatabaseMetadata rivuDatabase  = null ;
			
			try{
				conn = DriverManager.getConnection(database.getDatabaseurl(), database.getAccount(),RivuTools.decryption(database.getPassword())) ;
				rivuDatabase = new RivuDatabaseMetadata(conn,ParamTools.getProperties(ParamTools.getProperties(null , database.getConnectparam() , database.getAccount()),database.getConnectparam(),database.getAccount())) ;
				tables = rivuDatabase.loadTables(null, null, null, true) ;
			}finally{
				if(conn!=null)
					conn.close() ;
			}
		}
		
		return tables;
	}
	/**
	 * 
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public static List<RivuTableMetaData> getTables(Database database,String tabltableNamePattern) throws Exception{
		if(database==null)
			return new ArrayList<RivuTableMetaData>();
		List<RivuTableMetaData> tables = null ;
		{
			Connection conn = null ;
			RivuDatabaseMetadata rivuDatabase  = null ;
			
			try{
				DruidDataSource dataSource = DataSourceTools.createDataSource(database);
				conn = dataSource.getConnection();
				rivuDatabase = new RivuDatabaseMetadata(conn,ParamTools.getProperties(ParamTools.getProperties(null , database.getConnectparam() , database.getAccount()),database.getConnectparam(),database.getAccount())) ;
				tables = rivuDatabase.loadTables(tabltableNamePattern, null, null, true) ;
			}finally{
				if(conn!=null)
					conn.close() ;
			}
		}
		
		return tables;
	}
	/**
	 * 
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public static RivuTableMetaData getTable(Database database , String tablename) throws Exception{
		RivuTableMetaData rivuTableMetaData = null ;
		{
			Connection conn = null ;
			RivuDatabaseMetadata rivuDatabase  = null ;
			try{
				DruidDataSource dataSource = DataSourceTools.createDataSource(database);
				conn = dataSource.getConnection();
				rivuDatabase = new RivuDatabaseMetadata(conn,ParamTools.getProperties(ParamTools.getProperties(null , database.getConnectparam() , database.getAccount()),database.getConnectparam(),database.getAccount())) ;
				rivuTableMetaData = rivuDatabase.loadTable(tablename, null, null, true) ;
			}catch(Exception ex){
				ex.printStackTrace();
				throw ex;
			}finally{
				conn.close() ;
			}
		}
			
		return rivuTableMetaData;
	}
	
	/**
	 * 
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public static List<List<ValueData>> getTableData(Database database , String sql , RivuTableMetaData metadata) throws Exception{
		Connection conn = null ;
		List<List<ValueData>> valueList = new ArrayList<List<ValueData>>() ;
		try{
			conn = DriverManager.getConnection(database.getDatabaseurl(), database.getAccount(),RivuTools.decryption(database.getPassword())) ;
			PreparedStatement statement = conn.prepareStatement(sql) ;
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next()){
				List<ValueData> valueDataList = new ArrayList<ValueData>();
				for(RivuColumnMetadata column : metadata.getColumnMetadatas()){
					valueDataList.add(new ValueData(column.getName(), resultSet.getObject(column.getName()),String.valueOf(resultSet.getObject(column.getName())) , column.getTypeName())) ;
				}
				valueList.add(valueDataList) ;
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			conn.close() ;
		}

		return valueList ;
	}
	
	/**
	 * 
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public static List<List<ValueData>> getTableData(Database database , String sql) throws Exception{
		Connection conn = null ;
		List<List<ValueData>> valueList = new ArrayList<List<ValueData>>() ;
		try{
			conn = DriverManager.getConnection(database.getDatabaseurl(), database.getAccount(),RivuTools.decryption(database.getPassword())) ;
			PreparedStatement statement = conn.prepareStatement(sql) ;
			ResultSet resultSet = statement.executeQuery();
			while(resultSet.next()){
				List<ValueData> valueDataList = new ArrayList<ValueData>();
				ResultSetMetaData meta = resultSet.getMetaData() ;
				for(int i=1; i<=meta.getColumnCount() ; i++){
					valueDataList.add(new ValueData(meta.getColumnName(i), resultSet.getObject(meta.getColumnName(i)),String.valueOf(resultSet.getObject(meta.getColumnName(i))) , meta.getColumnTypeName(i))) ;
				}
				valueList.add(valueDataList) ;
			}
		}catch(Exception ex){
			throw ex;
		}finally{
			conn.close() ;
		}

		return valueList ;
	}
	/**
	 * 根据Cube获取处理后的SQL
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public static String getCubeSQL(Cube cube , String orgi , TableTask tableTask) throws Exception{
		StringBuffer strb = new StringBuffer() , table = new StringBuffer() , group = new StringBuffer() , field = new StringBuffer();
		strb.append("SELECT ") ;
		int i=0 ;
		CubeMetadata mainMeta = null ;
		for(CubeMetadata meta : cube.getMetadata()){
			i++;
			meta.setNamealias("tb_"+i) ;
			if("0".equals(meta.getMtype()) || cube.getMetadata().size()==1)
			{
				mainMeta = meta ;
				if("2".equals(meta.getTb().getTabletype())){
					table.insert(0,"("+meta.getTb().getDatasql()+") "+meta.getNamealias()) ;
				}else{
					table.insert(0,meta.getTb().getName()+ " " +meta.getNamealias()) ;
				}
			}
		}
		if(mainMeta==null){
			throw new Exception("主表未设置，请选择主表");
		}
		for(Dimension dim : cube.getDimension()){
			for(CubeLevel level :dim.getCubeLevel()){
				CubeMetadata meta = getMeta(level.getTableproperty().getId() , null , cube) ;
				if(meta != null){
					if(group.length()>0){
						group.append(",") ;
					}
					if(field.length()>0){
						field.append(",") ;
					}
					if(level.getAttribue()==null || level.getAttribue().trim().length()==0){
						if("2".equals(meta.getTb().getTabletype())){
							String str = new StringBuffer().append(meta.getNamealias()).append(".").append(level.getCode()).append(" AS DIM_").append(level.getNameAlias().toUpperCase()).toString() ;
							group.append(meta.getNamealias()).append(".").append(level.getCode());
							field.append(str) ;
						}else{
							String str = new StringBuffer().append(meta.getNamealias()).append(".").append(level.getCode()).append(" AS DIM_").append(level.getNameAlias().toUpperCase()).toString() ;
							group.append(meta.getNamealias()).append(".").append(level.getCode());
							field.append(str) ;
						}
					}else{
						group.append(getSQLText(level.getAttribue()));
						field.append(getSQLText(level.getAttribue())).append(" AS DIM_").append(level.getNameAlias().toUpperCase()).append(" ") ;
					}
				}
			}
		}
		for(CubeMeasure measure : cube.getMeasure()){
			if(measure.isCalculatedmember()){
				continue ;
			}
			CubeMetadata meta = getMeta(measure.getColumname() , measure.getTablename() , cube) ;
			if(meta!=null){
				if(field.length()>0){
					field.append(",") ;
				}
				if(measure.getAttribue()==null || measure.getAttribue().trim().length()==0){
					if("2".equals(meta.getTb().getTabletype())){
						//group.append(meta.getNamealias()).append(".").append(measure.getCode()) ;
						
						field.append(getAggregator(measure.getAggregator() , new StringBuffer().append(meta.getNamealias()).append(".").append(measure.getCode()).toString() , measure)) ;
					}else{
						//group.append(measure.getTablename()).append(".").append(measure.getCode()) ;
						field.append(getAggregator(measure.getAggregator() , new StringBuffer().append(meta.getNamealias()).append(".").append(measure.getCode()).toString(), measure)) ;
					}
				}else{
					//group.append(getSQLText(measure.getAttribue())) ;
					
//					field.append(getAggregator(measure.getAggregator() , new StringBuffer().append(getSQLText(measure.getAttribue())).append(" AS ").append("MEA_").append(measure.getNameAlias()).append(" ").toString(), measure)) ;
					field.append(getAggregator(measure.getAggregator() , new StringBuffer().append(getSQLText(measure.getAttribue())).append(" ").toString(), measure)) ;

				}
			}
		}
		
		strb.append(field.toString()).append(" FROM ");
		if(!tableTask.isUserpage()){
			StringBuffer innerSQL = new StringBuffer() ;
			innerSQL.append("select * from ").append( table.toString())  ;
			
			tableTask.setDatasql(innerSQL.toString()) ;
			Properties properties = new Properties();
			properties.put("dialect", tableTask.getDatabase().getSqldialect()) ;
			TabelSql tableSQL = getSQL(properties , tableTask , 0 , 1) ;
			
			strb.append("(").append(tableSQL.getSql()).append(") tb_1 ") ;
		}else{
			strb.append(table.toString()) ;
		}
		int inx = 1 ;
		
		for(Dimension dim : cube.getDimension()){
			if(StringUtils.isNotBlank(dim.getFkfield()) && StringUtils.isNotBlank(dim.getFktable()) && StringUtils.isNotBlank(dim.getFktableid())){
				inx ++ ;
				strb.append(" LEFT JOIN ").append(dim.getFktable()).append(" ").append("tb_").append(inx).append(" ON tb_1.").append(dim.getFkfield()).append(" = ").append("tb_").append(inx).append(".").append(dim.getFktableid()).append(" ") ;
			}
		}
		
		return strb.append(" GROUP BY ").append(group.toString()).toString() ;
	}
	
	
	/**
	 * 补充表属性的原始名称
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public static void addPropertiesOldName(Cube cube , String orgi , TableTask tableTask) throws Exception{
		if(tableTask.getTableproperty()!=null&&tableTask.getTableproperty().size()>0){
			for (TableProperties tporp : tableTask.getTableproperty()) {
				for(Dimension dim : cube.getDimension()){
					for(CubeLevel level :dim.getCubeLevel()){
						if(tporp.getName().equals("DIM_"+level.getNameAlias().toUpperCase())){
							tporp.setGroupid(level.getCode());
						}
					}
				}
				for(CubeMeasure measure : cube.getMeasure()){
					if(measure.isCalculatedmember()){
						continue ;
					}
					if(tporp.getName().equals("MEA_"+measure.getNameAlias().toUpperCase())){
						tporp.setGroupid(measure.getCode());
					}
				}
			}
			
		}
		
	}
	
	
	/**
	 * 
	 * @param type
	 * @param sql
	 * @return
	 */
	private static String getAggregator(String type , String sql , CubeMeasure measure){
		String retSQL  = sql ;
		if("sum".equals(type)){
			retSQL = new StringBuffer().append(" SUM(").append(sql).append(") ").append(" AS MEA_").append(measure.getNameAlias().toUpperCase()).toString();
		}
		if("count".equals(type)){
			retSQL = new StringBuffer().append(" COUNT(").append(sql).append(") ").append(" AS MEA_").append(measure.getNameAlias().toUpperCase()).toString();
		}
		if("min".equals(type)){
			retSQL = new StringBuffer().append(" MIN(").append(sql).append(") ").append(" AS MEA_").append(measure.getNameAlias().toUpperCase()).toString();
		}
		if("max".equals(type)){
			retSQL = new StringBuffer().append(" MAX(").append(sql).append(") ").append(" AS MEA_").append(measure.getNameAlias().toUpperCase()).toString();
		}
		if("avg".equals(type)){
			retSQL = new StringBuffer().append(" AVG(").append(sql).append(") ").append(" AS MEA_").append(measure.getNameAlias().toUpperCase()).toString();
		}
		if("distinct-count".equals(type)){
			retSQL = new StringBuffer().append(" COUNT( DISTINCT ").append(sql).append(") ").append(" AS MEA_").append(measure.getNameAlias().toUpperCase()).toString();
		}
		return retSQL ;
	}
	/**
	 * 
	 * @param tablepropertyid
	 * @param cube
	 * @return
	 */
	private static CubeMetadata getMeta(String tableproperty , String tableName , Cube cube){
		CubeMetadata retMeta = null ;
		for(CubeMetadata meta : cube.getMetadata()){
			for(TableProperties tp : meta.getTb().getTableproperty()){
				if(tableproperty.equals(tp.getId()) || (tp.getFieldname().equalsIgnoreCase(tableproperty))){
					retMeta = meta;
					break ;
				}
			}
		}
		return retMeta ;
	}
	
	private static String getSQLText(String sql){
		java.util.regex.Pattern pattern = java.util.regex.Pattern.compile("<SQL[\\S\\s]*?>([\\S\\s]*?)</SQL>") ;
		Matcher matcher = pattern.matcher(sql) ; 
		if(matcher.find() && matcher.groupCount()>=1){
			sql = matcher.group(1) ;
		}
		return  sql;
	}
	/**
	 * 
	 * @param database
	 * @return
	 * @throws Exception
	 */
	public static RivuTableMetaData getSQL(TableTask tableTask , String taskName,Database database , String datasql) throws Exception{
		RivuTableMetaData rivuTableMetaData = null ;
		{
			Connection conn = null ;
			RivuDatabaseMetadata rivuDatabase  = null ;
			Statement statement = null ;
			try{
				DruidDataSource dataSource = DataSourceTools.createDataSource(database);
				conn = dataSource.getConnection();
				rivuDatabase = new RivuDatabaseMetadata(conn,ParamTools.getProperties(ParamTools.getProperties(null , database.getConnectparam() , database.getAccount()),database.getConnectparam(),database.getAccount())) ;
				
				Properties proeperties = new Properties() ;
				proeperties.put("dialect", database.getSqldialect()) ;
				proeperties.put("num", "1") ;
				TabelSql tableSQL = getSQL(proeperties  , tableTask , 0 , 1) ;
				log.info("获取数据表结构："+tableSQL.getSql()) ;
				
				statement = conn.createStatement() ;
				rivuTableMetaData = rivuDatabase.loadSQL(statement, tableSQL.getSql() , taskName , null , database.getAccount() , true) ;
			}catch(Exception ex){
				ex.printStackTrace();
				throw ex;
			}finally{
				if(statement!=null)
					statement.close();
				if(conn!=null){
					conn.close() ;
				}
			}
		}
			
		return rivuTableMetaData;
	}

	/**
	 * 
	 * @param properties
	 * @return
	 * @throws Exception 
	 */
	public static ResultSet getTableData(Connection conn , PreparedStatement statment , Properties properties , TableTask table) throws Exception{
			ResultSet rs = null ;
			try{
				rs = statment.executeQuery() ;
			}catch(Exception ex){
				ex.printStackTrace();
			}finally{
			
			}
		return rs ;
	}
	/**
	 * 
	 * @param properties
	 * @param table
	 * @param startindex
	 * @param endindex
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public synchronized static TabelSql getSQL(Properties properties , TableTask table , long startindex , long endindex) throws InstantiationException, IllegalAccessException, ClassNotFoundException{
		return getSQL(properties , table , startindex , endindex , null ,  null , null);
	}
	/**
	 * 
	 */
	private static SimpleDateFormat defaultFormat = new SimpleDateFormat("yyyyMMdd");
	/**
	 * 计算 SQL中的参数
	 * @param table
	 * @return
	 */
	public static String getDataSQL(TableTask table){
		return getDataSQL(table.getFormatDatasql(), table.getPreviewtemplet()) ;
	}
	
	/**
	 * 计算 SQL中的参数
	 * @param table
	 * @return
	 */
	public static String getDataSQL(String datasql , String defaultParamJSON){
		return getDataSQL(datasql, defaultParamJSON , null) ;
	}
	
	/**
	 * 计算 SQL中的参数
	 * @param table
	 * @return
	 */
	public static String getDataSQL(String datasql , String defaultParamJSON , Map<String ,Object> requestValue){
		Map<String,Object> values = new HashMap<String,Object>();
		values.put("T", Integer.parseInt(defaultFormat.format(new Date()))) ;
		values.put("t", Integer.parseInt(defaultFormat.format(new Date()))) ;
		User user = (User)(SecurityUtils.getSubject().getSession().getAttribute(RivuDataContext.USER_SESSION_NAME)) ;
		values.put("user",user) ;

		if(requestValue!=null){
			values.putAll(requestValue) ;
		}
		
		if(defaultParamJSON!=null && defaultParamJSON.length()>0){
			DefaultParam defaultParam = JSON.parseObject(defaultParamJSON, DefaultParam.class) ;
			if(defaultParam.getValues().size()>0){
				for(DefaultValue value : defaultParam.getValues()){
					if(!value.getValue().equalsIgnoreCase("null")){
						try {
							if(value.getValue()!=null && value.getValue().matches("[ ]{0,}([TtMmYy]{1,})[ ]{0,}[+-]{0,1}([\\d]{0,})")){
								values.put(value.getName(), RivuTools.formatDateValue(defaultParam , RivuTools.getDaysParam(value.getValue()))) ;
							}else{
								values.put(value.getName(), value.getValue()) ;
							}
						} catch (ParseException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
		try {
			return RivuTools.getTemplet(datasql, values);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (TemplateException e) {
			e.printStackTrace();
		}
		return datasql ;
	}
	/**
	 * 
	 * @param properties
	 * @param table
	 * @param startindex
	 * @param endindex
	 * @param field
	 * @param value
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
	public synchronized static TabelSql getSQL(Properties properties , TableTask table , long startindex , long endindex , String field , String value , String areatype) throws InstantiationException, IllegalAccessException, ClassNotFoundException{
		TabelSql tableSQL = null ;
		String dataSQL = getDataSQL(table) ;
		TableProperties moditField = null ;
		if(table!=null && table.getTableproperty()!=null){
			for(TableProperties tp : table.getTableproperty()){
				if(moditField == null && tp.getModits()){
					moditField = tp ;
					break ;
				}
			}
		}
		if(properties.getProperty("dialect")!=null && !table.isUserpage()){
			long len = 0 ;
			if(endindex>0 && endindex > startindex){
				len = endindex - startindex ;
			}else{
				len = properties.getProperty("num")!=null && properties.getProperty("num").matches("[\\d]*") ? Integer.parseInt(properties.getProperty("num")) : 50000 ;
			}
			String dialect = table.getDatabase().getSqldialect()!=null ? table.getDatabase().getSqldialect() : properties.getProperty("dialect") ;
			
			if("sqlserver".equalsIgnoreCase(dialect)){
				StringBuffer strb = new StringBuffer() ;
				strb.append("SELECT TOP ").append(len).append(" ") ;
				TableProperties sortTP = null ,pk = null;
//				int i= 0;
				for(TableProperties tp : table.getTableproperty()){
					if(tp!=null){
						if(tp.getPk()){
							sortTP = tp ;
							pk = tp ;
						}
						if(sortTP == null){
							sortTP = tp ;
						}
					}
				}
				strb.append(" * FROM ") ;
				if(table.getTabletype().equals("1")){
					strb.append(table.getTablename()).append("  AS rivu_sql_innertable_1") ;
				}else{
					strb.append("(").append(dataSQL).append(") AS rivu_sql_innertable_1")  ;
				}
				if(table.getUserid()!=null && table.getUserid().equals("1") && pk!=null){
					strb.append(" WHERE ").append(pk.getFieldname()).append(">?") ;
					strb.append(" ORDER BY ").append(pk.getFieldname()).append(" asc") ;
					tableSQL = new TabelSql(strb.toString(),false , 1, startindex , moditField);
				}else if(moditField!=null){
					strb.append(" WHERE ").append(moditField.getFieldname()).append(">?") ;
					strb.append(" ORDER BY ").append(moditField.getFieldname()).append(" asc") ;
					tableSQL = new TabelSql(strb.toString(),false , 1, moditField!=null?table.getLastupdate():null , moditField);
				}else{
					strb.append(" WHERE [").append(sortTP.getName()).append("] NOT IN (") ;
					strb.append(" SELECT TOP ").append(startindex).append(" [").append(sortTP.getName()).append("] FROM ") ;
					if(table.getTabletype().equals("1")){
						strb.append(table.getTablename()).append("  AS rivu_sql_innertable_2") ;
					}else{
						strb.append("(").append(dataSQL).append(") AS rivu_sql_innertable_2")  ;
					}
					strb.append(")") ;
					tableSQL = new TabelSql(strb.toString(),false , 1, null , null );
				}
				
			}else if("oracle".equalsIgnoreCase(dialect)){
				StringBuffer strb = new StringBuffer() ;
				strb.append("select * from ( select row_.*, rownum rownum_ from ( ");
				if(table.getTabletype().equals("1")){
					strb.append(table.getTablename()) ;
				}else{
					strb.append(dataSQL) ;
				}
				if(moditField!=null){
					strb.append(" WHERE ").append(moditField.getFieldname()).append(">?") ;
					strb.append(" ORDER BY ").append(moditField.getFieldname()).append(" asc") ;
					
					strb.append(") row_ where rownum <= ") ;
					strb.append(len) ;
					strb.append(") where rownum_ > 0") ;
					tableSQL = new TabelSql(strb.toString(),false , 1, moditField!=null?table.getLastupdate():null , moditField);
				}else{
					strb.append(")");
					if(field!=null && value!=null && areatype!=null){
						if(areatype.equals("date")){
							strb.append("field = date '").append(value).append("'") ;
						}else if(areatype.equals("number")){
							strb.append("field = ").append(value);
						}else{
							strb.append("field = '").append(value).append("'");
						}
					}
					strb.append(" row_ where rownum <= ") ;
					strb.append(startindex+len) ;
					strb.append(") where rownum_ > ").append(startindex) ;
					tableSQL = new TabelSql(strb.toString(),false , 1, null , null );
				}
			}else if("mysql".equalsIgnoreCase(dialect)){
				StringBuffer strb = new StringBuffer() ;
				strb.append("select * from ");
				if(table.getTabletype().equals("1")){
					strb.append(table.getTablename()) ;
				}else{
					strb.append("(").append(dataSQL).append(") AS rivu_sql_innertable_2 ") ;
				}
				if(moditField!=null){
					strb.append(" where `").append(moditField.getFieldname()).append("`>?") ;
					strb.append(" order by `").append(moditField.getFieldname()).append("` asc ") ;
					strb.append(" limit ").append(0).append(",").append(len);
					
					tableSQL = new TabelSql(strb.toString(),false , 1, moditField!=null?table.getLastupdate():null , moditField);
				}else{
					strb.append("limit ").append(startindex).append(",").append(len);
					tableSQL = new TabelSql(strb.toString(),false , 1, null , null );
				}
			}else if("postgresql".equalsIgnoreCase(dialect)){
				StringBuffer strb = new StringBuffer() ;
				strb.append("select * from ( ");
				if(table.getTabletype().equals("1")){
					strb.append(table.getTablename()) ;
				}else{
					strb.append("(").append(dataSQL).append(") AS rivu_sql_innertable_2 ") ;
				}
				strb.append(") ");
				if(moditField!=null){
					strb.append(" where `").append(moditField.getFieldname()).append("`>?") ;
					strb.append(" order by `").append(moditField.getFieldname()).append("` asc ") ;
					strb.append(" limit ").append(len).append(" offset ").append(0);
					
					tableSQL = new TabelSql(strb.toString(),false , 1, moditField!=null?table.getLastupdate():null , moditField);
				}else{
					strb.append("limit ").append(len).append(" offset ").append(startindex);
					tableSQL = new TabelSql(strb.toString(),false , 1, null , null );
				}
			}else if("hive".equalsIgnoreCase(dialect)){
				StringBuffer strb = new StringBuffer()  , tpField = new StringBuffer() ;
				
				if(table.getTabletype().equals("1")){
					strb.append("select ");
					for(TableProperties tp : table.getTableproperty()){
						if(tp!=null){
							if(tpField.length()>0){
								tpField.append(",") ;
							}
							tpField.append(tp.getFieldname()) ;
						}
					}
					if(tpField.length()>0){
						strb.append(tpField.toString()) ;
					}else{
						strb.append(" * ") ;
					}
					strb.append(" from ");
					strb.append(table.getTablename()) ;
				}else{
					strb.append(dataSQL) ;
				}
				strb.append(" limit ").append(startindex) ;
				tableSQL = new TabelSql(strb.toString(),false , 1, null , null );
			}
		}
		if(tableSQL==null){
			StringBuffer strb = new StringBuffer() ;
			strb.append("select ") ;
			
			strb.append("*") ;
			if(table.getTabletype().equals("1")){
				strb.append(" from ").append(table.getTablename()) ;
			}else{
				strb.append(" from (").append(dataSQL).append(") rivu_sql_innertable") ;
			}
			if(moditField!=null){
				strb.append(" where ").append(moditField.getFieldname()).append(">?") ;
				strb.append(" order by ").append(moditField.getFieldname()).append(" asc") ;
			}
			String sql = strb.toString() ;
	//		if(dialetInstance.supportsLimitOffset()){
	//			sql = dialetInstance.getLimitString(strb
	//					.toString(), job.getStartindex(), RivuDataContext
	//					.getDefaultCacheRec(table.getConfigure())) ;
	//		}else{
	//			sql = strb.toString() ;
	//		}
	//		TabelSql tableSQL = new TabelSql(sql,dialetInstance.supportsLimit() , dialetInstance.supportsLimit()&& moditField!=null?3:dialetInstance.supportsLimit()&&moditField==null?2:!dialetInstance.supportsLimit()&&moditField!=null?1:0, moditField!=null?table.getUpdatetime():null);
			
			tableSQL = new TabelSql(sql,false , 1, moditField!=null?table.getLastupdate():null , moditField);
		}
		
		return  tableSQL;
	}
	public static void main(String[] args){
		try {
			Calendar calendar = Calendar.getInstance() ;
			System.out.println(calendar.get(Calendar.YEAR)*12 + calendar.get(Calendar.MONTH )+1);
//			table.setTablename("XDTECHSITE") ;
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
